package org.abettor.nookinfo.persist;

import java.lang.reflect.Method;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;

/**
 * 数据库访问工具类
 * @author shawn
 *
 */
public class DatabaseOpenHelper extends SQLiteOpenHelper
{
	/** 本地数据库名 */
	public static final String DATABASE_NAME = "nookinfo.db";
	/** 当前数据库版本号 */
	public static final int DATABASE_VERSION = 1;

	public DatabaseOpenHelper(Context context, String name, CursorFactory factory, int version)
	{
		super(context, name, factory, version);
	}

	@Override
	public void onCreate(SQLiteDatabase db)
	{
		//	创建 tbl_novel
		String tblNovel = "CREATE TABLE tbl_novel (";
		tblNovel += "n_id INTEGER PRIMARY KEY NOT NULL,";
		tblNovel += "c_name VARCHAR(255) NOT NULL)";
		db.execSQL(tblNovel);
		String idxNovel = "CREATE INDEX idx_novel_name on tbl_novel (c_name asc)";
		db.execSQL(idxNovel);

		//	创建 tbl_novel_chapter
		String tblNovelChapter = "CREATE TABLE tbl_novel_chapter (";
		tblNovelChapter += "n_id INTEGER PRIMARY KEY NOT NULL,";
		tblNovelChapter += "n_novel INTEGER NOT NULL,";
		tblNovelChapter += "n_order INTEGER NOT NULL,";
		tblNovelChapter += "c_name VARCHAR(255) NOT NULL)";
		db.execSQL(tblNovelChapter);
		String idxNovelChapter = "CREATE INDEX idx_novel_chapter_order on tbl_novel_chapter (n_novel asc, n_order asc)";
		db.execSQL(idxNovelChapter);

		//	创建 tbl_last_read
		String tblLastRead = "CREATE TABLE tbl_last_read (";
		tblLastRead += "n_id INTEGER NOT NULL,";
		tblLastRead += "n_local BOOL NOT NULL,";
		tblLastRead += "c_category VARCHAR(255) NOT NULL,";
		tblLastRead += "c_novel VARCHAR(255) NOT NULL,";
		tblLastRead += "PRIMARY KEY (n_id, n_local))";
		db.execSQL(tblLastRead);

		//	创建 tbl_photo_category
		String tblPhotoCategory = "CREATE TABLE tbl_photo_category (";
		tblPhotoCategory += "n_id INTEGER NOT NULL,";
		tblPhotoCategory += "c_name VARCHAR(255) NOT NULL,";
		tblPhotoCategory += "c_description VARCHAR(255),";
		tblPhotoCategory += "c_update VARCHAR(19) NOT NULL)";
		db.execSQL(tblPhotoCategory);
		String idxPhotoCategory = "CREATE INDEX idx_photo_category_update on tbl_photo_category (c_update desc)";
		db.execSQL(idxPhotoCategory);
		idxPhotoCategory = "CREATE INDEX idx_photo_category_id on tbl_photo_category (n_id asc)";
		db.execSQL(idxPhotoCategory);

		//	创建 tbl_photo
		String tblPhoto = "CREATE TABLE tbl_photo (";
		tblPhoto += "n_order INTEGER PRIMARY KEY NOT NULL,";
		tblPhoto += "n_category INTEGER NOT NULL,";
		tblPhoto += "n_photo INTEGER NOT NULL)";
		db.execSQL(tblPhoto);
		String idxPhoto = "CREATE INDEX idx_photo_category on tbl_photo (n_category asc)";
		db.execSQL(idxPhoto);
	}

	/**
	 * 用户数据库升级的方法。<br/>
	 * 每个数据库升级的动作为本类的私有方法，方法的命名规则为<b>doUpdate+&lt;旧版本号&gt;</b>的形式。<br/>
	 * 如：<i><pre>
	 * 	@SuppressWarnings("unused")
	 *  private void doUpdate1(SQLiteDatabase db)
	 *  {
	 *  	//	将数据库从版本1升级到版本2的工作
	 *  }
	 * </pre></i><br/>
	 * 每个升级方法仅负责将该版本的数据库升级到下一临近版本，无需升级的版本对应方法可以不写。
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
	{
		Class<?> clazz = this.getClass();
		try
		{
			for(int i = oldVersion; i < newVersion; i ++)
			{
				String methodName = "doUpdate" + i;
				Method method = clazz.getDeclaredMethod(methodName, new Class<?>[]{SQLiteDatabase.class});
				if(method == null)
				{
					continue;
				}

				method.setAccessible(true);
				method.invoke(this, new Object[]{db});
			}
		}
		catch(Exception e)
		{
			Log.d(clazz.getName(), "", e);
		}
	}
}
